const mysql = require('mysql2/promise');
require('dotenv').config();

async function updatePaymentsTable() {
  let connection;
  
  try {
    // 创建数据库连接
    connection = await mysql.createConnection({
      host: process.env.DB_HOST || 'localhost',
      user: process.env.DB_USER || 'root',
      password: process.env.DB_PASS || '',
      database: process.env.DB_NAME || 'llz_project'
    });
    
    console.log('数据库连接成功');
    
    // 检查prepay_id字段是否已存在
    const [columns] = await connection.execute(
      "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 'payments' AND COLUMN_NAME = 'prepay_id'",
      [process.env.DB_NAME || 'llz_project']
    );
    
    if (columns.length > 0) {
      console.log('prepay_id字段已存在，无需更新');
      return;
    }
    
    // 添加prepay_id字段
    await connection.execute(
      "ALTER TABLE payments ADD COLUMN prepay_id VARCHAR(64) NULL COMMENT '微信支付预支付ID' AFTER payment_no"
    );
    console.log('✓ 成功添加prepay_id字段');
    
    // 添加索引
    try {
      await connection.execute(
        "CREATE INDEX idx_payments_prepay_id ON payments(prepay_id)"
      );
      console.log('✓ 成功创建prepay_id索引');
    } catch (indexError) {
      if (indexError.code === 'ER_DUP_KEYNAME') {
        console.log('prepay_id索引已存在');
      } else {
        throw indexError;
      }
    }
    
    // 显示更新后的表结构
    const [tableStructure] = await connection.execute('DESCRIBE payments');
    console.log('\n更新后的payments表结构:');
    console.table(tableStructure);
    
    console.log('\npayments表更新成功！');
    
  } catch (error) {
    console.error('更新payments表失败:', error.message);
    process.exit(1);
  } finally {
    if (connection) {
      await connection.end();
      console.log('数据库连接已关闭');
    }
  }
}

// 执行更新
updatePaymentsTable();